import requests
import json
import time
import pymysql
import traceback  # 追踪异常


def get_tencent_date():
    """
    return：返回历史数据和当日详细数据
    """

    url = "https://view.inews.qq.com/g2/getOnsInfo?name=disease_other"
    headers = {
        "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36"
    }
    r = requests.get(url, headers=headers)
    drs = json.loads(r.text)
    data_all = json.loads(drs["data"])

    history = {}  # 历史数据
    for i in data_all['chinaDayList']:
        ds = "2020" + i["date"]
        # print(ds)
        tup = time.strptime(ds, "%Y%m.%d")
        ds = time.strftime("%Y-%m-%d", tup)
        confirm = i["confirm"]
        suspect = i['suspect']
        heal = i["heal"]
        dead = i["dead"]
        history[ds] = {"confirm": confirm, "suspect": suspect, "heal": heal, "dead": dead}
    for i in data_all["chinaDayAddList"]:
        ds = "2020" + i["date"]
        tup = time.strptime(ds, "%Y%m.%d")
        ds = time.strftime("%Y-%m-%d", tup)
        confirm = i["confirm"]
        suspect = i['suspect']
        heal = i["heal"]
        dead = i["dead"]
        history[ds].update({"confirm": confirm, "suspect": suspect, "heal": heal, "dead": dead})

    new_url = "https://view.inews.qq.com/g2/getOnsInfo?name=disease_h5"
    new_headers = {
        "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36"
    }
    rr = requests.get(new_url, headers=new_headers)
    des = json.loads(rr.text)
    # print(d["data"])
    data_all = json.loads(des["data"])

    details = []  # 当日详细数据
    update_time = data_all["lastUpdateTime"]
    data_country = data_all["areaTree"]  # list25个国家
    data_preovince = data_country[0]["children"]  # 中国各个省份
    for pro_infos in data_preovince:
        province = pro_infos["name"]  # 省名
        for city_infos in pro_infos["children"]:
            city = city_infos["name"]
            confirm = city_infos["total"]["confirm"]  # 累加病例
            confirm_add = city_infos["today"]["confirm"]  # 今日添加
            heal = city_infos["total"]["heal"]
            dead = city_infos["total"]["dead"]
            details.append([update_time, province, city, confirm, confirm_add, heal, dead])

    return history, details




#连接数据库
def get_conn():
    """
    return:连接，游标
    """
    #创建连接
    conn = pymysql.connect(host="localhost",user="root", password="mysql", db="cov",charset="utf8")

    #创建游标
    cursor = conn.cursor()  #执行完毕返回的结果集默认以元组显示
    return conn, cursor

#关闭
def close_conn(conn, cursor):  #关闭
    if(cursor!=None):
        cursor.close()
    if conn !=None:
        conn.close()


def insert_history():
    """
    插入历史数据
    """
    cursor = None
    conn = None
    try:
        dic = get_tencent_date()[0]
        print(f"{time.asctime()}开始插入历史数据！")
        conn, cursor = get_conn()
        sql = "insert into history values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        for k, v in dic.items():
            # item格式{2020-6-26:{"confirm":41}}
            cursor.execute(sql, [k, v.get("confirm"), v.get("confirm_add"), v.get("suspect"), v.get("suspect_add"),
                                 v.get("heal"), v.get("heal_add"), v.get("dead"), v.get("dead_add")])

        conn.commit()
        print(f"{time.asctime()}插入历史数据完毕！")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)




def update_details():
    """
    更新details表
    return
    """
    cursor = None
    conn = None
    try:
        li = get_tencent_date()[1]  #0是历史历史数据字典，1是最新详细的数据列表(return返回的值)
        conn,cursor = get_conn() # 调用 get_conn方法返回对应的值
        sql = "insert into details(update_time,province,city,confirm,confirm_add,heal,dead) values(%s,%s,%s,%s,%s,%s,%s)"
        #以id的降序排序，拿最后一条记录的时间。 相等就返回1，否则返回0
        sql_query = "select %s=(select update_time from details order by id desc limit 1)"#对比最大的时间戳
        cursor.execute(sql_query,li[0][0])
        if not cursor.fetchone()[0]:
            print(f"{time.asctime()}开始更新最新数据")
            for item in li:
                cursor.execute(sql, item)
            conn.commit()  #提交事务 update delete inser操作
            print(f"{time.asctime()}更新最新数据完毕")
        else:
            print(f"{time.asctime()}已是最新数据！")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


def update_history():
    """
    更新历史数据
    :return:
    """
    cursor = None
    conn = None
    try:
        dic = get_tencent_date()[0]
        print(f"{time.asctime()}开始更新历史数据！")
        conn, cursor = get_conn()
        sql = "insert into history values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        sql_query = "select confirm from history where ds=%s"
        for k, v in dic.items():
            # item格式{2020-6-26:{"confirm":41}}
            if not cursor.execute(sql_query, k):
                cursor.execute(sql, [k, v.get("confirm"), v.get("confirm_add"), v.get("suspect"), v.get("suspect_add"),
                                     v.get("heal"), v.get("heal_add"), v.get("dead"), v.get("dead_add")])

            conn.commit()
            print(f"{time.asctime()}历史数据更新完毕！")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)


import time
from selenium.webdriver import Chrome, ChromeOptions


def get_baidu_hot():
    option = ChromeOptions()
    option.add_argument("--headless")  # 隐藏浏览器
    option.add_argument("--no-sandbox")

    browser = Chrome(options=option)
    url = "https://voice.baidu.com/act/virussearch/virussearch?from=osari_map&tab=0&infomore=1"
    browser.get(url)
    # print(browser.page_source)
    btn = browser.find_element_by_css_selector(
        "#ptab-0 > div > div.VirusHot_1-5-6_32AY4F.VirusHot_1-5-6_2RnRvg > section > div")
    btn.click()  # 点击
    time.sleep(3)
    c = browser.find_elements_by_xpath('//*[@id="ptab-0"]/div/div[1]/section/a/div/span[2]')
    context = [i.text for i in c]
    print(context)
    return context



def update_hotsearch():
    """
    将疫情热搜插入数据库
    """
    cursor = None
    conn = None
    try:
        context = get_baidu_hot()
        print(f"{time.asctime()}开始更新热搜数据")
        conn, cursor = get_conn()
        sql = "insert into hotsearch(dt, content) values(%s,%s)"
        ts = time.strftime("%Y-%m-%d %X")
        for i in context:
            cursor.execute(sql,(ts,i)) #插入数据
        conn.commit()  #提交事务保存数据
        print(f"{time.asctime()}数据更新完毕")
    except:
        traceback.print_exc()
    finally:
        close_conn(conn, cursor)

if __name__ == '__main__':
    get_tencent_date()
    insert_history()
    update_details()
    update_history()

    get_baidu_hot()
    update_hotsearch()
